package com.aaa.javaweb.until;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;

public class JDBCUntil {
    public static  String DRIVER;
    public static  String URL;
    public static  String ROOT;
    public static  String PASSWORD;

    //静态的加载驱动方法
    static {
        try {
            InputStream stream = JDBCUntil.class.getResourceAsStream("/d.properties");
            System.out.println(stream);
            Properties p = new Properties();
            p.load(stream);
            DRIVER = (String) p.get("driver");
            URL = (String) p.get("url");
            ROOT = (String) p.get("root");
            PASSWORD = (String) p.get("password");

            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    //创建链接
    public static Connection cjlj(){
        Connection conn =null;
        try {
            conn = DriverManager.getConnection(URL, ROOT, PASSWORD);

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return conn;
    }

    //关闭通道
    public static void closeAll(ResultSet r1, PreparedStatement p1,Connection conn){
        try {
            if (r1!=null){
                r1.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


        try {
            if(p1!=null){
                p1.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        try {
            if (conn!=null){
                conn.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    //创建增删改方法
    public static int Updateout(String sql,Object... objects){
        Connection conn = cjlj();
        PreparedStatement p1 =null;
        try {
            p1 = conn.prepareStatement(sql);
            if(objects.length>0){
                for (int i = 1; i <= objects.length ; i++) {
                    p1.setObject(i,objects[i-1]);
                }
            }
            int i = p1.executeUpdate();
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(null,p1,conn);
        }
        return -1;
    }

    //创建查询返回List<List>方法
    public static List<List> Querylist(String sql,Object... objects){
        Connection conn = cjlj();
        ResultSet r1 =null;
        PreparedStatement p1 =null;
        ArrayList<List> biglist = new ArrayList<>();
        try {
            p1 = conn.prepareStatement(sql);
            if (objects.length>0){
                for (int i =1;i<= objects.length;i++){
                    p1.setObject(i,objects[i-1]);
                }
            }
            r1 = p1.executeQuery();
            while(r1.next()){
                ArrayList<Object> smallllist = new ArrayList<>();
                for (int i = 1; i <=r1.getMetaData().getColumnCount() ; i++) {
                    smallllist.add(r1.getObject(i));
                }
                biglist.add(smallllist);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(r1,p1,conn);
        }
        return biglist;
    }

        //查询返回List<Map>类型的方法
    public static List<Map> selectMap(String sql,Object... objects){
        Connection conn = cjlj();
        ArrayList<Map> biglist = new ArrayList<>();
        PreparedStatement p1 =null;
        ResultSet r1 =null;
        try {
            p1 = conn.prepareStatement(sql);
            if(objects.length>0){
                for (int i = 1; i <= objects.length ; i++) {
                    p1.setObject(i,objects[i-1]);
                }
            }
            r1 = p1.executeQuery();
            while (r1.next()){
                HashMap<String, Object> map = new HashMap<>();
                for (int i = 1; i <=r1.getMetaData().getColumnCount() ; i++) {
                    map.put(r1.getMetaData().getColumnName(i),r1.getObject(i));
                }
                biglist.add(map);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll(r1,p1,conn);
        }
        return biglist;
    }


        //返回键值对的方法
        public static int Updatekeys(String sql,Object... objects){
            Connection conn = cjlj();
            PreparedStatement p1 =null;
            ResultSet gkeys =null;
            try {
                p1 = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
                if (objects.length>0){
                    for (int i = 1; i <= objects.length ; i++) {
                        p1.setObject(i,objects[i-1]);
                    }
                }
                p1.executeUpdate();
                gkeys = p1.getGeneratedKeys();
                if (gkeys.next()){
                    int anInt = gkeys.getInt(1);
                    return anInt;
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                closeAll(gkeys,p1,conn);
            }
            return -1;
        }
}
